package webServices.utility;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import static webServices.utility.StaticFields.*;


public class CreateDB {

	/**
	 * @param args
	 */
	private final static String 	application="APPLICATION", 
									logData="LOG_DATA",
									rawData="RAW_DATA",
									intContactType="INT_CONTACT_TYPE",
									intState="INT_STATE",
									interlocutor="INTERLOCUTOR",
									client="CLIENT",
									intContact="INT_CONTACT",
									ws="WS",
									wsByApplication="WS_BY_APPLICATION",
									wsOperation="WS_OPERATION",
									wsOperationPar="WS_OPERATION_PAR",
									event="EVENT",
									eventParameter="EVENT_PARAMETER",
									slaNS="SLA_NS",
									slaState="SLA_STATE",
									provider="PROVIDER",
									sla="SLA",
									violation="VIOLATION";
									
	
	private final static String typeKey="bigint";
private final static String key=typeKey+" not null auto_increment";
	
	public static void main(String[] args) {
		try {
			Statement stmt;
			Class.forName("com.mysql.jdbc.Driver");
			String urlJdbc =
				"jdbc:mysql://localhost:3306/"+StaticFields.MYSQL_DB;
			Connection con =DriverManager.getConnection(
						urlJdbc,StaticFields.MYSQL_USER, StaticFields.MYSQL_PASS);
			stmt = con.createStatement();
			String url0="sla1.plasticxmi";
			String url1="airlineSLA.plasticxmi";
			String[] url={url0, url1};
			
			dropTableRAWLOG(stmt);
			createRAWLOG(stmt);
			dropTable(stmt);
			createTable(stmt);
			setInsert(stmt); 
		//	deleteTableEvent(stmt);
			stmt.executeBatch();
   
			insertSLA(stmt, url);

			con.close();
			System.out.println("wrote...");
		}catch( Exception e ) {
			e.printStackTrace();
		}//end catch
	}//end main
	
	private static void deleteTableEvent(Statement stmt) throws SQLException {
		stmt.addBatch(
				"DELETE FROM "+logData);
		stmt.addBatch(
				"DELETE FROM "+eventParameter);
		stmt.addBatch(
				"DELETE FROM "+violation);
		stmt.addBatch(
				"DELETE FROM "+event);
	}
	
	private static void deleteTable(Statement stmt) throws SQLException {
		/*application
		intContactType
		intState
		interlocutor
		user
		intContact
		ws
		wsByApplication
		wsOperation
		wsOperationPar*/

		stmt.addBatch(
				"DELETE FROM "+intContactType);
		stmt.addBatch(
				"DELETE FROM "+intState);
		stmt.addBatch(
				"DELETE FROM "+application);
		stmt.addBatch(
				"DELETE FROM "+interlocutor);
		
		stmt.addBatch(
				"DELETE FROM "+client);
		stmt.addBatch(
				"DELETE FROM "+intContact);
		
		stmt.addBatch(
				"DELETE FROM "+wsByApplication);
		stmt.addBatch(
				"DELETE FROM "+wsOperation);
		stmt.addBatch(
				"DELETE FROM "+wsOperationPar);
		stmt.addBatch(
				"DELETE FROM "+ws);

	}

	private static void deleteTableRAWLOG(Statement stmt) throws SQLException {
		stmt.addBatch(
				"DELETE FROM "+rawData);
		stmt.addBatch(
				"DELETE FROM "+logData);
	}

	private static void dropTable(Statement stmt) throws SQLException {
		stmt.addBatch(
		        "DROP TABLE "+
		        wsByApplication+", "+
		        violation+", "+
		        eventParameter+", " +
		        
		        event+"," +
		        wsOperationPar+", " +
		        wsOperation+", " +
		        ws+", " +
		        
		        sla+"," +
		        slaState+", " +
		        slaNS+", " +
		        provider+", " +
		        client+", " +
		        intContact+", " +
		        interlocutor+", " +
		        intState+", " +
		        intContactType+", "+
		        application
		        );
	}


	private static void dropTableRAWLOG(Statement stmt) throws SQLException {
		stmt.addBatch(
		        "DROP TABLE "+
		        rawData+", " +
		        logData+"");
	}


	private static void createTable(Statement stmt) throws SQLException{

		stmt.addBatch(
				"CREATE TABLE "+application+" (" +
				" id_a "+key+"," +
				"name VARCHAR(80) NOT NULL," +
				"app_url VARCHAR(80) unique NOT NULL," +
				"PRIMARY KEY (id_a)" +
		") ENGINE=InnoDB");

		
		stmt.addBatch(
				"CREATE TABLE "+intContactType+" (" +
				"id_ict "+key+"," +
				"code CHAR(1) unique NOT NULL," +
				"description VARCHAR(20) NOT NULL," +
				"PRIMARY KEY (id_ict)" +
		") ENGINE=InnoDB");
		
		
		stmt.addBatch(
				"CREATE TABLE "+intState+" (" +
				"id_is "+key+"," +
				"code CHAR(1) unique NOT NULL," +
				"description VARCHAR(20) NOT NULL," +
				"PRIMARY KEY (id_is)" +
		") ENGINE=InnoDB");
		
		
		String keyInterlocutor1=interlocutor+"_fk1";
		stmt.addBatch(
				"CREATE TABLE "+interlocutor+" (" +
				"id_i "+key+"," +
				"int_state_id "+typeKey+" NOT NULL," +
				"PRIMARY KEY (id_i)," +
				"KEY "+keyInterlocutor1+" (int_state_id)," +
				"CONSTRAINT "+keyInterlocutor1+" FOREIGN KEY (int_state_id) REFERENCES "+intState+" (id_is) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
		
		
		String keyUser1=client+"_fk1";
		String idxUser1=client+"_idx1";
		stmt.addBatch(
				"CREATE TABLE "+client+" (" +
				"id_u "+key+"," +
				"username VARCHAR(80) unique NOT NULL," +
				"int_id "+typeKey+" NOT NULL," +
				"password VARCHAR(80) NOT NULL," +
				"PRIMARY KEY (id_u)," +
				"KEY "+keyUser1+" (int_id)," +
				"CONSTRAINT "+keyUser1+" FOREIGN KEY (int_id) REFERENCES "+interlocutor+" (id_i) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
		stmt.addBatch(
				"CREATE UNIQUE INDEX "+idxUser1+" ON "+client+" ( username, password )");
		
		
		String keyIntContact1=intContact+"_fk1";
		String keyIntContact2=intContact+"_fk2";
		stmt.addBatch(
				"CREATE TABLE "+intContact+" (" +
				"id_ic "+key+"," +
				"int_contact_type_id "+typeKey+" NOT NULL," +
				"value VARCHAR(20) NOT NULL," +
				"int_id "+typeKey+" NOT NULL," +
				"PRIMARY KEY (id_ic)," +
				"KEY "+keyIntContact1+" (int_contact_type_id)," +
				"CONSTRAINT "+keyIntContact1+" FOREIGN KEY (int_contact_type_id) REFERENCES "+intContactType+" (id_ict) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keyIntContact2+" (int_id)," +
				"CONSTRAINT "+keyIntContact2+" FOREIGN KEY (int_id)  REFERENCES "+interlocutor+" (id_i) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
		
		
		String idxWS1=ws+"_idx1";
		stmt.addBatch(
				"CREATE TABLE "+ws+" (" +
				"id_ws "+key+"," +
				"name VARCHAR(80) NOT NULL," +
				"description VARCHAR(80)," +
				"namespace VARCHAR(80) unique NOT NULL," +
				"PRIMARY KEY (id_ws)" +
		") ENGINE=InnoDB");
	/*	stmt.addBatch(
				"CREATE UNIQUE INDEX "+idxWS1+" USING BTREE ON "+ws+" (namespace )");
		*/
		
		String keyWsByApplication1=wsByApplication+"_fk1";
		String keyWsByApplication2=wsByApplication+"_fk2";
		stmt.addBatch(
				"CREATE TABLE "+wsByApplication+" (" +
                "id_wsba "+key+"," +
                "ws_id "+typeKey+" NOT NULL," +
                "application_id "+typeKey+" NOT NULL," +
                "PRIMARY KEY (id_wsba), " +
                "KEY "+keyWsByApplication1+" (application_id)," +
				"CONSTRAINT "+keyWsByApplication1+" FOREIGN KEY (application_id) REFERENCES "+application+" (id_a) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
                "KEY "+keyWsByApplication2+" (ws_id)," +
				"CONSTRAINT "+keyWsByApplication2+" FOREIGN KEY (ws_id) REFERENCES "+ws+" (id_ws) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");

		
		String keyWsOperation1=wsOperation+"_fk1";
		String idxWSOperation1=wsOperation+"_idx1";
		stmt.addBatch(
				"CREATE TABLE "+wsOperation+" (" +
				"id_wso "+key+"," +
				"name VARCHAR(80) NOT NULL," +
				"ws_id "+typeKey+" NOT NULL," +
				"PRIMARY KEY (id_wso)," +
				"KEY "+keyWsOperation1+" (ws_id)," +
				"CONSTRAINT "+keyWsOperation1+" FOREIGN KEY (ws_id) REFERENCES "+ws+" (id_ws) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
		stmt.addBatch(
				"CREATE UNIQUE INDEX "+idxWSOperation1+" USING BTREE ON "+wsOperation+" (name, ws_id)");
		
		
		String keyWsOperationPar1=wsOperationPar+"_fk1";
		stmt.addBatch(
				"CREATE TABLE "+wsOperationPar+" (" +
				"id_wsop "+key+"," +
				"name VARCHAR(80)," +
                "position INT(255) NOT NULL," +
                "mandatory CHAR(1) NOT NULL," +
                "ws_operation_id "+typeKey+" NOT NULL," +
                "PRIMARY KEY (id_wsop)," +
				"KEY "+keyWsOperationPar1+" (ws_operation_id)," +
				"CONSTRAINT "+keyWsOperationPar1+" FOREIGN KEY (ws_operation_id) REFERENCES "+wsOperation+" (id_wso) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");


		String keyEvent1=event+"_fk1";
		String keyEvent2=event+"_fk2";
		String keyEvent3=event+"_fk3";
		String keyEvent4=event+"_fk4";
		stmt.addBatch(
				"CREATE TABLE "+event+" (" +
				"id_e VARBINARY("+KEY_SIZE+") not null unique," +
				"direction CHAR(1) NOT NULL," +
                "timestamp BIGINT NOT NULL," +
                "seq_number BIGINT NOT NULL," +
                "session VARCHAR(32) NOT NULL," +
                "application_id "+typeKey+" NOT NULL," +
                "user_id "+typeKey+" NOT NULL," +
                "ws_operation_id "+typeKey+" NOT NULL," +
                "ws_id "+typeKey+" NOT NULL," +
				"PRIMARY KEY (id_e)," +
				"KEY "+keyEvent1+" (ws_operation_id)," +
				"CONSTRAINT "+keyEvent1+" FOREIGN KEY (ws_operation_id) REFERENCES "+wsOperation+" (id_wso) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keyEvent2+" (ws_id)," +
				"CONSTRAINT "+keyEvent2+" FOREIGN KEY (ws_id) REFERENCES "+ws+" (id_ws) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keyEvent3+" (user_id)," +
				"CONSTRAINT "+keyEvent3+" FOREIGN KEY (user_id) REFERENCES "+client+" (id_u) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keyEvent4+" (application_id)," +
				"CONSTRAINT "+keyEvent4+" FOREIGN KEY (application_id) REFERENCES "+application+" (id_a) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");

		
		String keyEventParameter1=eventParameter+"_fk1";
		String keyEventParameter2=eventParameter+"_fk2";
		stmt.addBatch("CREATE TABLE "+eventParameter+" (" +
				"id_ep "+key+"," +
				"value VARCHAR(20)," +
				"position INT(255) NOT NULL," +
				"ws_operation_par_id "+typeKey+" NOT NULL," +
				"event_id VARBINARY("+KEY_SIZE+") NOT NULL," +
				"PRIMARY KEY (id_ep)," +
				"KEY "+keyEventParameter1+" (ws_operation_par_id), " +
				"CONSTRAINT "+keyEventParameter1+" FOREIGN KEY (ws_operation_par_id) REFERENCES "+wsOperationPar+" (id_wsop) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keyEventParameter2+" (event_id), " +
				"CONSTRAINT "+keyEventParameter2+" FOREIGN KEY (event_id) REFERENCES "+event+" (id_e) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
		
		
		stmt.addBatch(
				"CREATE TABLE "+slaNS+" (" +
				"id_slans "+key+"," +
				"controller_classpath VARCHAR(100) NOT NULL," +
				"name VARCHAR(80) NOT NULL," +
				"PRIMARY KEY (id_slans)" +
		") ENGINE=InnoDB");
		
		
		stmt.addBatch(
				"CREATE TABLE "+slaState+" (" +
				"id_slas "+key+"," +
				"code CHAR(1) unique NOT NULL," +
				"description VARCHAR(20) NOT NULL," +
				"PRIMARY KEY (id_slas)" +
		") ENGINE=InnoDB");

		
		String keyClient1=provider+"_fk1";
		stmt.addBatch(
				"CREATE TABLE "+provider+" (" +
				"id_c "+key+"," +
				"int_id "+typeKey+" NOT NULL," +
				"name VARCHAR(80) NOT NULL," +
				"PRIMARY KEY (id_c)," +
				"KEY "+keyClient1+" (int_id)," +
				"CONSTRAINT "+keyClient1+" FOREIGN KEY (int_id) REFERENCES "+interlocutor+" (id_i) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
		
		
		String keySla1=sla+"_fk1";
		String keySla2=sla+"_fk2";
		String keySla3=sla+"_fk3";
		String keySla4=sla+"_fk4";
		String keySla5=sla+"_fk5";
		stmt.addBatch(
				"CREATE TABLE "+sla+" (" +
				"id_sla "+key+"," +
				"SLA BLOB NOT NULL," +
				"SLA_state_id "+typeKey+" NOT NULL," +
				"SLA_NS_id "+typeKey+" NOT NULL," +
				"provider_id "+typeKey+" NOT NULL," +
				"application_id "+typeKey+" NOT NULL," +
				"user_id "+typeKey+" NOT NULL," +
				"PRIMARY KEY (id_sla)," +
				"KEY "+keySla1+" (application_id)," +
				"CONSTRAINT "+keySla1+" FOREIGN KEY (application_id) REFERENCES "+application+" (id_a) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keySla2+" (SLA_NS_id)," +
				"CONSTRAINT "+keySla2+" FOREIGN KEY (SLA_NS_id) REFERENCES "+slaNS+" (id_slans) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keySla3+" (SLA_state_id)," +
				"CONSTRAINT "+keySla3+" FOREIGN KEY (SLA_state_id) REFERENCES "+slaState+" (id_slas) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keySla4+" (provider_id)," +
				"CONSTRAINT "+keySla4+" FOREIGN KEY (provider_id) REFERENCES "+provider+" (id_c) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
				"KEY "+keySla5+" (user_id)," +
				"CONSTRAINT "+keySla5+" FOREIGN KEY (user_id) REFERENCES "+client+" (id_u) ON DELETE NO ACTION ON UPDATE NO ACTION" +
		") ENGINE=InnoDB");
	

	String keyViolation1=violation+"_fk1";
	String keyViolation2=violation+"_fk2";
	stmt.addBatch("CREATE TABLE "+violation+ " (" +
			"id_v "+key+","+
			"componentName VARCHAR(80) NOT NULL, "+
			"sla_id "+typeKey+" NOT NULL, "+
			"event_id VARBINARY("+KEY_SIZE+") NOT NULL, "+
			"description VARCHAR(300), "+
			"timestamp TIMESTAMP NULL DEFAULT NOW(), "+
			"PRIMARY KEY (id_v)," +
			"KEY "+keyViolation1+" (sla_id)," +
			"CONSTRAINT "+keyViolation1+" FOREIGN KEY (sla_id) REFERENCES "+sla+" (id_sla) ON DELETE NO ACTION ON UPDATE NO ACTION, " +
			"KEY "+keyViolation2+" (event_id)," +
			"CONSTRAINT "+keyViolation2+" FOREIGN KEY (event_id) REFERENCES "+event+" (id_e) ON DELETE NO ACTION ON UPDATE NO ACTION" +
	") ENGINE=InnoDB");	
	
	}
	private static void createRAWLOG(Statement stmt) throws SQLException{

		stmt.addBatch(
				"CREATE TABLE "+rawData+" (" +
				"id_key VARBINARY("+KEY_SIZE+") not null unique, " +
				"content VARBINARY(1024) NOT NULL, " +
				"PRIMARY KEY (id_key)" +
		")ENGINE=InnoDB");

		stmt.addBatch(
				"CREATE TABLE "+logData+" (" +
				"id_log "+key+"," +
				"size INT(255) NOT NULL," +
				"direction CHAR(1) NOT NULL," +
				"timestamp VARCHAR(80) NOT NULL," +
				"session VARCHAR(80) NOT NULL," +
				"seq_number VARCHAR(80)," +
				"username VARCHAR(80)," +
				"password VARCHAR(80)," +
				"app_url VARCHAR(80)," +
				"ws_namespace VARCHAR(80)," +
				"operation_name VARCHAR(80)," +
				"parameters_byte VARBINARY(500)," +
				"PRIMARY KEY (id_log)" +
		") ENGINE=InnoDB");
	}


	private static void setInsert(Statement stmt) throws SQLException{
		/*
		application
		intContactType
		intState
		interlocutor
		user
		intContact
		ws
		wsByApplication
		wsOperation
		wsOperationPar
		event
		eventParameter
		slaNS
		slaState
		client
		sla									       
*/	
	stmt.addBatch("INSERT INTO "+application+" (name, app_url) VALUES " +
															"('Tesi','http://localhost:8080/TesiWeb')");
	stmt.addBatch("INSERT INTO "+intContactType+" (code, description) VALUES " +
															"('0','email')," +
															"('1', 'mobile')");
	stmt.addBatch("INSERT INTO "+intState+" (code, description) VALUES " +
															"('0','attivo')," +
															"('1','sospeso')");
	stmt.addBatch("INSERT INTO "+interlocutor+" (int_state_id) VALUES " +
															"('1'), " +
															"('2'), "+
															"('1')");
	stmt.addBatch("INSERT INTO "+client+" (username, int_id, password) VALUES " +
															"('paolo.rossi@gmail.com', '1', 'paolo.rossi'), " +
															"('luca.bianchi@gmail.com', '1', 'lucaBianchi'), " +
															"('ale.santo@gmail.com', '2', 'alessandro')");
	stmt.addBatch("INSERT INTO "+intContact+" (int_contact_type_id, value, int_id) VALUES " +
															"('1','info@gmail.com','1'), " +
															"('1','prova@gmail.com','2'), "+
															"('1','provider@gmail.com','3')");
	stmt.addBatch("INSERT INTO "+ws+" (name, namespace) VALUES " +
															"('Arithmetic Operations', 'http://www.operations.com/'), " +
															"('Test', 'http://www.test.com/'), " +
															"('Airline Reservation', 'http://www.airline.com/')");
	stmt.addBatch("INSERT INTO "+wsByApplication+" (ws_id, application_id) VALUES " +
															"('1', '1'), " +
															"('2', '1'), " +
															"('3', '1')");
	stmt.addBatch("INSERT INTO "+wsOperation+" (name,ws_id) VALUES " +
															"('addition', '1'), " +
															"('substraction', '1'), " +
															"('prova', '1'), " +
															"('prova', '2'), " +
															"('addition', '2'), " +
															"('checkFlights', '3'), " +
															"('getFlightDetails', '3'), " +
															"('cancelReservation', '3'), " +
															"('reserveFlight', '3'), " +
															"('confirmFlightReservation', '3'), " +
															"('getTicketDetails', '3')");
	stmt.addBatch("INSERT INTO "+wsOperationPar+" (name,position,mandatory,ws_operation_id) VALUES " +
															"('', '0', 'y', '1'), " +
															"('number2', '1', 'y', '1'), " +
															"('arg0', '0', 'y', '2'), " +
															"('number2', '1', 'y', '2'), " +
															"('number1', '0', 'y', '5'), " +
															"('number2', '1', 'y', '5')");
		stmt.addBatch("INSERT INTO "+slaState+" (code, description) VALUES " +
															"('0','attivo')," +
															"('1','sospeso')");			
		stmt.addBatch("INSERT INTO "+slaNS+" (controller_classpath, name) VALUES " +
				"('esempio','slang')" );
		stmt.addBatch("INSERT INTO "+provider+" (int_id, name) VALUES " +
		"('3','Provider Cancemi')" );
	}
	
	public static void insertSLA(Statement stmt, String[] url) throws SQLException{
		stmt.addBatch("DELETE FROM "+violation);
		stmt.addBatch("DELETE FROM "+sla);
		stmt.executeBatch();
		for(int i=0;i<url.length;i++){
			Blob blob = stmt.getConnection().createBlob();
			blob.setBytes(1,  getBytesFromFile(new File(url[i])));
			PreparedStatement ps=stmt.getConnection().prepareStatement("INSERT INTO "+sla+" (SLA, SLA_state_id,SLA_NS_id,provider_id,application_id,user_id) VALUES (?, ?, ?, ?, ?, ?)");
			ps.setBlob(1, blob);
			ps.setLong(2, 1);
			ps.setLong(3, 1);
			ps.setLong(4, 1);
			ps.setLong(5, 1);
			ps.setLong(6, i+1);
			int j = ps.executeUpdate();

			if(j>0) {
				System.out.println("Uploaded successfully !");
			}
			else {
				System.out.println("unsucessfull to upload image.");
			}
		}
	}
	
	public static byte[] getBytesFromFile(File file){
		byte[] bytes=null;
		try {
			InputStream is = new FileInputStream(file);

			// Get the size of the file
			long length = file.length();

			if (length > Integer.MAX_VALUE) {
				// File is too large
			}

			// Create the byte array to hold the data
			bytes = new byte[(int)length];

			// Read in the bytes
			int offset = 0;
			int numRead = 0;
			while (offset < bytes.length
					&& (numRead=is.read(bytes, offset, bytes.length-offset)) >= 0) {
				offset += numRead;
			}

			// Ensure all the bytes have been read in
			if (offset < bytes.length) {
				throw new IOException("Could not completely read file "+file.getName());
			}

			// Close the input stream and return bytes
			is.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return bytes;
	}
}


